var ASC_START = 65

function SUM(sheetName, startCell,endCell){
    let sum = 0
    var startRowAndColIndex = getRowAndColIndex(startCell)
    var endRowAndColIndex = getRowAndColIndex(endCell)
    for (var rowIndex = startRowAndColIndex[0] ; rowIndex <= endRowAndColIndex[0] ; rowIndex++){
        for (var colIndex = startRowAndColIndex[1] ; colIndex <= endRowAndColIndex[1] ; colIndex++){
            var cellName = getCellNameByIndex(rowIndex,colIndex);
            var sheetCellName = sheetName + "_" + cellName;
            sum += getExcelCellNumbericValue(sheetCellName)
        }
    }
    return sum
}

function getRowAndColIndex(cellName){
    var reg = /([A-z]+)(\d+)/;
    reg.test(cellName);
    var colName = RegExp.$1
    var rowIndex = parseInt(RegExp.$2)
    var colIndex = colName.charCodeAt() - ASC_START
    return [rowIndex, colIndex]
}

function getCellNameByIndex(rowIndex, colIndex){
    return String.fromCharCode(colIndex + ASC_START) + rowIndex
}

/**
 * 获取数值型cell值
 * @param {格式：sheet0_A1} id 
 */
function getExcelCellValue(id) {
    var cell = document.getElementById(id);
    var son = cell.children[0];
    var cellValue;
    if(son){
        cellValue = son.value;
        var cellType = cell.getAttribute("cell_type");
        if (cellType === "num"){
            return parseInt(cellValue) || 0;
        }else if (cellType === "float"){
            return parseFloat(cellValue) || 0.0;
        }else if (cellType === "str"){
            return cellValue + "";
        }else if (cellType === "bool"){
            return cellValue == "true";
        }else if(cellType === "date"){
            return null;
        }else {
            return getValueByAnalyseType(cellValue)
        }
    }else{
        return getValueByAnalyseType(cell.innerHTML);
    }
}

function getValueByAnalyseType(cellValue){
    if(typeof cellValue == "number" || /(\d+)|(\d+)\.(\d+)/.test(cellValue)){
        if(/./.test(cellValue)){
            return parseFloat(cellValue);
        }else{
            return parseFloat(cellValue);
        }
    }else if(typeof cellValue == "string"){
        return cellValue
    }else if(typeof cellValue == "boolean"){
        return cellValue == "true";
    }else{
        return null;
    }
}

/**
 * 获取整型
 * @param {*} id 
 */
function getExcelCellNumbericValue(id) {
    var cell = document.getElementById(id);
    if (!cell) {
        return 0;
    } else {
        if(cell.children[0]){
            return parseInt(cell.children[0].value) || 0;
        }else{
            return parseInt(cell.innerHTML) || 0;
        }
    }
}

/**
 * 获取浮点型
 * @param {*} id 
 */
function getExcelCellFloatValue(id) {
    var cell = document.getElementById(id);
    if (!cell || !cell.value) {
        return 0.0;
    } else {
        return parseFloat(cell.value);
    }
}


/**
 * 获取字符串
 * @param {*} id 
 */
function getExcelCellStringValue(id) {
    var cell = document.getElementById(id);
    if (!cell || !cell.value) {
        return "";
    } else {
        return "" + cell.value;
    }
}


function cellValueFormatting(cellValue, formatString){
    if(formatString == "#,##0.00_ "){
        if(/\./.test(cellValue)){
            return parseFloat(cellValue).toFixed(2)
        }else{
            return parseInt(cellValue).toFixed(2)
        }
    }else if(formatString == "0%"){
        console.log(cellValue, formatString)
    }else{
        return cellValue;
    }
}